package com.gogowan.agroenterprise.db;

import java.util.ArrayList;
import java.util.Currency;
import java.util.List;

import com.gogowan.agroenterprise.model.CollectCode;
import com.gogowan.agroenterprise.model.CollectData;
import com.gogowan.agroenterprise.model.MaterialBase;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper {

	public static final String DB_DBNAME = "zypt_db";

	public static final String DB_DATA_DICTINARY = "dictinary";// 基础字典

	public static final String DB_DATA_OPERATION = "operation";// 操作记录

	public static final int VERSION = 1;

	public static SQLiteDatabase dbInstance;

	private MyDBHelper myDBHelper;

	private Context context;

	public DBHelper(Context context) {
		this.context = context;
	}

	public void openDatabase() {
		if (dbInstance == null) {
			myDBHelper = new MyDBHelper(context, DB_DBNAME, VERSION);
			dbInstance = myDBHelper.getWritableDatabase();
		}
	}

	class MyDBHelper extends SQLiteOpenHelper {

		public MyDBHelper(Context context, String name, int version) {
			super(context, name, null, version);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// 数据字典表
			String dictinaryCreate = "CREATE TABLE IF NOT EXISTS " + DB_DATA_DICTINARY + " ( " + "materialId LONG , "// 产品编号
					+ "materialName VARCHAR,"// 产品名称
					+ "categoryCode VARCHAR,"// 品类编码
					+ "scategoryCode VARCHAR,"// 品类简码
					+ "materialSpection VARCHAR,"// 产品规格
					+ "temp1 VARCHAR,"// 预留字段
					+ "temp2 VARCHAR,"// 预留字段
					+ "temp3 VARCHAR)";// 预留字段
			db.execSQL(dictinaryCreate);
			// 操作字典表
			String operationCreate = "CREATE TABLE IF NOT EXISTS " + DB_DATA_OPERATION + " ( " + "materialId LONG , "// 产品编号
					+ "materialName VARCHAR,"// 产品名称
					+ "materialCode VARCHAR,"// 产品码
					+ "materialScode VARCHAR,"// 产品码（截取）
					+ "categoryCode VARCHAR,"// 品类编码
					+ "scategoryCode VARCHAR,"// 品类简码
					+ "materialSpection VARCHAR,"// 产品规格
					+ "codeStr VARCHAR,"// 码号
					+ "productTime VARCHAR,"// 生产时间
					+ "scanTime VARCHAR,"// 扫码时间
					+ "orderTime VARCHAR,"// 建单时间
					+ "operMan VARCHAR,"// 操作人
					+ "collectUserId VARCHAR,"// 操作人编号
					+ "temp1 VARCHAR,"// 预留字段
					+ "temp2 VARCHAR,"// 预留字段
					+ "temp3 VARCHAR,"// 预留字段
					+ "temp4 VARCHAR,"// 预留字段
					+ "temp5 VARCHAR)";// 预留字段
			db.execSQL(operationCreate);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// String sql = "drop table if exists " + DB_TABLENAME_GJTK;
			// db.execSQL(sql);
			// myDBHelper.onCreate(db);
		}
	}

	// 初始化数据字典
	public void initDataDictinary(List<MaterialBase> datas) {
		deleteData();
		for (MaterialBase item : datas) {
			ContentValues values = new ContentValues();
			values.put("materialId", item.getMaterialId());
			values.put("materialName", item.getMaterialName());
			values.put("categoryCode", item.getCategoryCode());
			values.put("scategoryCode", item.getSCategoryCode());
			values.put("materialSpection", item.getMaterialSpection());
			values.put("temp1", item.getMaterialCode());
			values.put("temp2", "");
			values.put("temp3", "");
			dbInstance.insert(DB_DATA_DICTINARY, null, values);
		}
	}

	// 获取数据字典信息
	public List<MaterialBase> getBaseData() {
		List<MaterialBase> lists = new ArrayList<>();
		String sql = "select materialId,materialName,categoryCode,scategoryCode,materialSpection from dictinary";
		Cursor cursor = null;
		try {
			cursor = dbInstance.rawQuery(sql, null);
			cursor.moveToPosition(-1);
			while (cursor.moveToNext()) {
				MaterialBase itemdata = new MaterialBase();
				itemdata.setMaterialId(cursor.getLong(0));
				itemdata.setMaterialName(cursor.getString(1));
				itemdata.setCategoryCode(cursor.getString(2));
				itemdata.setSCategoryCode(cursor.getString(3));
				itemdata.setMaterialSpection(cursor.getString(4));
				lists.add(itemdata);
			}

		} catch (Exception e) {
			lists.clear();
		} finally {
			if (cursor != null)
				cursor.close();
			// dbInstance.close();
		}
		return lists;
	}

	// 获取数据字典信息
	public List<String> getScanList() {
		List<String> lists = new ArrayList<>();
		String sql = "select materialCode from operation";
		Cursor cursor = null;
		try {
			cursor = dbInstance.rawQuery(sql, null);
			cursor.moveToPosition(-1);
			while (cursor.moveToNext()) {
				lists.add(cursor.getString(0));
			}

		} catch (Exception e) {
			lists.clear();
		} finally {
			if (cursor != null)
				cursor.close();
			// dbInstance.close();
		}
		return lists;
	}

	// 查询产品信息
	public CollectData getResultData(String code, int type) {
		CollectData bean = null;
		String sql = null;
		if (type == 0) {
			sql = "select materialId,materialName,categoryCode,scategoryCode,materialSpection from dictinary where categoryCode = ?";
		} else if (type==1){
			sql = "select materialId,materialName,categoryCode,scategoryCode,materialSpection from dictinary where scategoryCode = ?";
		}else {
			sql = "select materialId,materialName,categoryCode,scategoryCode,materialSpection from dictinary where temp1 = ?";
		}
		String[] args = { code };
		Cursor cursor = null;
		try {
			cursor = dbInstance.rawQuery(sql, args);
			cursor.moveToPosition(-1);
			while (cursor.moveToNext()) {
				bean = new CollectData();
				bean.setMaterialId(cursor.getLong(0));
				bean.setMaterialName(cursor.getString(1));
				bean.setCategoryCode(cursor.getString(2));
				bean.setSCategoryCode(cursor.getString(3));
				bean.setMaterialSpection(cursor.getString(4));
			}

		} catch (Exception e) {
			bean = null;
		} finally {
			if (cursor != null)
				cursor.close();
			// dbInstance.close();
		}
		return bean;
	}

	// 保存扫码数据
	public void insertScanData(List<CollectData> datas, String time) {
		deleteByDate(time);
		for (CollectData item : datas) {
			ContentValues values = new ContentValues();
			values.put("materialId", item.getMaterialId());
			values.put("materialName", item.getMaterialName());
			values.put("materialCode", item.getMaterialCode());
			values.put("materialScode", item.getMaterialScode());
			values.put("categoryCode", item.getCategoryCode());
			values.put("scategoryCode", item.getSCategoryCode());
			values.put("materialSpection", item.getMaterialSpection());
			values.put("codeStr", item.getCodeStr());
			values.put("productTime", item.getProductTime());
			values.put("scanTime", item.getScanTime());
			values.put("orderTime", item.getOrderTime());
			values.put("operMan", item.getOperMan());
			values.put("collectUserId", item.getCollectUserId());
			values.put("temp1", "");
			values.put("temp2", "");
			values.put("temp3", "");
			values.put("temp4", "");
			values.put("temp5", "");
			dbInstance.insert(DB_DATA_OPERATION, null, values);
		}
	}

	// 保存扫码数据
	public boolean insertCollectData(CollectData item) {
		ContentValues values = new ContentValues();
		values.put("materialId", item.getMaterialId());
		values.put("materialName", item.getMaterialName());
		values.put("materialCode", item.getMaterialCode());
		values.put("materialScode", item.getMaterialScode());
		values.put("categoryCode", item.getCategoryCode());
		values.put("scategoryCode", item.getSCategoryCode());
		values.put("materialSpection", item.getMaterialSpection());
		values.put("codeStr", item.getCodeStr());
		values.put("productTime", item.getProductTime());
		values.put("scanTime", item.getScanTime());
		values.put("orderTime", item.getOrderTime());
		values.put("operMan", item.getOperMan());
		values.put("collectUserId", item.getCollectUserId());
		long i = dbInstance.insert(DB_DATA_OPERATION, null, values);
		if (i > 0) {
			return true;
		} else {
			return false;
		}
	}

	// 清除数据字典
	public int deleteData() {
		return dbInstance.delete(DB_DATA_DICTINARY, null, null);
	}

	// 按码号删除操作记录
	public int deleteByCode(String code) {
		return dbInstance.delete(DB_DATA_OPERATION, "codeStr = ?", new String[] { code });
	}
	//时间
    public int deleteBydata(String code) {
        return dbInstance.delete(DB_DATA_OPERATION, "orderTime = ?", new String[] { code });
    }
	public List<CollectCode> getHistroyList() {
		// MaterialCode MaterialScode tCodeStr ScanTime
		List<CollectCode> strings = new ArrayList<>();
		String sql1 = "select a.orderTime,a.operMan,(select count(*) from operation b where b.orderTime = a.orderTime ) as num from operation a group by a.orderTime,a.operMan";
		try {
			Cursor cursor1 = dbInstance.rawQuery(sql1, null);
			while (cursor1.moveToNext()) {
				CollectCode collectCode = new CollectCode();
				collectCode.setCollectTime(cursor1.getString(0));
				collectCode.setCollectUserName(cursor1.getString(1));
				collectCode.setBqsum(Integer.valueOf(cursor1.getString(2)));
				strings.add(collectCode);
			}
		} catch (Exception e) {
			String s = e.getLocalizedMessage();
		}
		return strings;
	}
	public List<CollectData> getMaterialBase(String time, String Man) {
		List<CollectData> collectCodes = new ArrayList<>();
		// materialId,MaterialCode,MaterialName,CategoryCode,SCategoryCode,MaterialSpection,MaterialScode
		String sql = "select materialId,materialCode,materialName,categoryCode,scategoryCode,materialSpection,materialScode,collectUserId,codeStr from operation where  orderTime=? AND operMan=?";
		String[] args = { time, Man };
		try {
			Cursor cursor1 = dbInstance.rawQuery(sql, args);
			while (cursor1.moveToNext()) {
				CollectData data = new CollectData();
				data.setMaterialId(cursor1.getLong(0));
				data.setMaterialCode(cursor1.getString(1));
				data.setMaterialName(cursor1.getString(2));
				data.setCategoryCode(cursor1.getString(3));
				data.setSCategoryCode(cursor1.getString(4));
				data.setMaterialSpection(cursor1.getString(5));
				data.setMaterialScode(cursor1.getString(6));
				data.setCollectUserId(cursor1.getString(7));
                data.setCodeStr(cursor1.getString(8));
				collectCodes.add(data);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return collectCodes;
	}

	// 按时间删除操作记录
	public int deleteByDate(String time) {
		return dbInstance.delete(DB_DATA_OPERATION, "orderTime = ?", new String[] { time });
	}

}
